import pandas as pd
import os
import csv
import sys
import argparse
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.filters import FilterColumn, CustomFilter, CustomFilters


def create_filename(filename):
    """
    创建唯一的文件名，如果文件已存在则添加序号
    相当于JavaScript中的createFileName函数
    """
    name = filename
    count = 1
    base_name, extension = os.path.splitext(filename)
    while os.path.exists(name):
        name = f"{base_name}({count}){extension}"
        count += 1
    return name


def compiler_excel(file_path=""):
    """
    读取Excel文件并转换为JSON对象数组
    相当于JavaScript中的complierExcel函数
    """
    print(f"读取文件: {file_path}")
    # 读取Excel文件
    df = pd.read_excel(file_path)
    
    # 获取第一行作为映射
    map_row = df.iloc[0].to_dict()
    
    # 创建新的数组（列表）
    new_arr = []
    
    # 从第二行开始处理数据
    for i in range(1, len(df)):
        item = df.iloc[i].to_dict()
        obj = {}
        for key in item.keys():
            if key in map_row and not pd.isna(map_row[key]):
                label = map_row[key]
                # 处理包含"支付订单号"的标签
                if isinstance(label, str) and "支付订单号" in label:
                    label = "支付订单号"
                obj[label] = item[key]
        new_arr.append(obj)
    
    return new_arr


def get_column_index(worksheet, column_name):
    """
    获取列名对应的索引
    相当于JavaScript中的getColumnIndex函数
    """
    for i, cell in enumerate(next(worksheet.iter_rows(min_row=1, max_row=1))):
        if cell.value == column_name:
            return i
    return -1


def export_excel(data_sheets, export_file_name):
    """
    导出数据到Excel文件，支持自动筛选功能
    相当于JavaScript中的exportExcel函数
    """
    # 创建一个新的Excel工作簿
    workbook = Workbook()
    
    # 如果传入的是单个数据集而非对象
    if not isinstance(data_sheets, dict) or 'sheets' not in data_sheets:
        # 获取默认的工作表
        worksheet = workbook.active
        if worksheet:
            worksheet.title = "Sheet1"
        
        # 添加表头
        if data_sheets and len(data_sheets) > 0:
            headers = list(data_sheets[0].keys())
            for col_idx, header in enumerate(headers, 1):
                worksheet.cell(row=1, column=col_idx, value=header)
            
            # 添加数据
            for row_idx, row_data in enumerate(data_sheets, 2):
                for col_idx, header in enumerate(headers, 1):
                    worksheet.cell(row=row_idx, column=col_idx, value=row_data.get(header, ""))
    else:
        # 处理多个工作表
        # 删除默认创建的Sheet
        default_sheet = workbook.active
        if default_sheet is not None:
            workbook.remove(default_sheet)
        
        for sheet in data_sheets['sheets']:
            # 创建新的工作表
            worksheet = workbook.create_sheet(title=sheet['name'])
            
            # 添加表头
            if sheet['data'] and len(sheet['data']) > 0:
                headers = list(sheet['data'][0].keys())
                for col_idx, header in enumerate(headers, 1):
                    worksheet.cell(row=1, column=col_idx, value=header)
                
                # 添加数据
                for row_idx, row_data in enumerate(sheet['data'], 2):
                    for col_idx, header in enumerate(headers, 1):
                        worksheet.cell(row=row_idx, column=col_idx, value=row_data.get(header, ""))
                
                # 添加自动筛选功能
                if 'options' in sheet and sheet['options'].get('autoFilter'):
                    # 设置筛选范围
                    max_row = len(sheet['data']) + 1  # +1 是因为有表头
                    max_col = len(headers)
                    filter_range = f"A1:{get_column_letter(max_col)}{max_row}"
                    worksheet.auto_filter.ref = filter_range
                    
                    # 如果有特定列的筛选条件
                    if isinstance(sheet['options']['autoFilter'], dict) and 'column' in sheet['options']['autoFilter']:
                        filter_column = sheet['options']['autoFilter']['column']
                        col_idx = get_column_index(worksheet, filter_column)
                        
                        if col_idx != -1:
                            # 在openpyxl中，列索引从0开始
                            col_letter = get_column_letter(col_idx + 1)
                            
                            # 创建筛选条件
                            filter_column = FilterColumn(colId=col_idx)
                            custom_filter = CustomFilter(operator='equal', val='1')
                            filter_column.customFilters = CustomFilters([custom_filter])
                            worksheet.auto_filter.filterColumn.append(filter_column)
    
    # 保存工作簿
    workbook.save(export_file_name)
    print(f"Excel 文件导出成功！{export_file_name}")


def get_csv_data(file_path):
    """
    读取CSV文件数据
    相当于JavaScript中的getCSVData函数
    """
    results = []
    try:
        with open(file_path, 'r', encoding='utf-8') as csvfile:
            csv_reader = csv.DictReader(csvfile)
            for row in csv_reader:
                results.append(row)
        print(f"CSV 读取完成，结果：{len(results)}")
        return results
    except Exception as e:
        print(f"读取错误: {e}")
        return []


def process_data(data, header, need_num, condition):
    """
    处理数据的通用函数
    相当于JavaScript中的processData函数
    """
    result = []
    for item in data:
        if item.get("支付状态") in ["部分退款", "已支付"] and condition(item):
            obj = {}
            for key in header:
                obj[key] = item.get(key, "")
                if key in need_num:
                    try:
                        obj[key] = float(obj[key])
                    except (ValueError, TypeError):
                        obj[key] = 0.0
            result.append(obj)
    return result


def currency_sum(data, cur_field="货币单位", sum_field="订单总价"):
    """
    计算不同货币的总和
    相当于JavaScript中的currencySum函数
    """
    currency_obj = {}
    for item in data:
        currency = item.get(cur_field)
        if currency:
            amount = item.get(sum_field, 0)
            try:
                amount = float(amount)
            except (ValueError, TypeError):
                amount = 0
            
            currency_obj[currency] = currency_obj.get(currency, 0) + amount
    
    print("货币汇总:")
    for currency, amount in currency_obj.items():
        print(f"{currency}: {amount}")
    print("------------------------------------")


def main(excel_files, pay_csv_file, air_csv_file, export_file_name):
    """
    主函数，处理Excel和CSV数据并导出结果
    相当于JavaScript中的main函数
    """
    # 读取Excel文件
    new_data = []
    for file_path in excel_files:
        data = compiler_excel(file_path)
        new_data.extend(data)
    
    # 定义表头
    header = [
        "支付订单号",
        "货币单位",
        "订单总价",
        "商品总价",
        "优惠金额",
        "物流费用",
        "支付状态",
        "支付方式1",
        "支付渠道1",
        "支付金额1",
    ]
    need_num = [header[2], header[3], header[4], header[5]]
    
    # 筛选条件1：部分退款或已支付，且支付渠道不是空中云汇
    pay_data = process_data(new_data, header, need_num, lambda item: item.get("支付渠道1") != "空中云汇")
    
    # 筛选条件2：对应云汇
    air_data = process_data(new_data, header, need_num, lambda item: item.get("支付渠道1") == "空中云汇")
    
    # 定义CSV数据的表头
    header1 = ["交易号", "类型", "状态", "币种", "总额", "费用", "净额"]
    checkout_types = ["快速结账付款", "直接信用卡付款", "预先批准付款账单用户付款"]
    
    header2 = ["交易编号", "授权状态返回描述", "交易状态", "交易币种", "交易金额"]
    checkout_types2 = ["FULLY REFUNDED", "PARTIALLY REFUNDED", "SUCCEEDED", "DISPUTED"]
    
    # 读取CSV数据
    csv_data = get_csv_data(pay_csv_file)
    
    # 检查是否存在华宁PayPal数据文件
    # current_dir = os.path.dirname(os.path.abspath(__file__))
    # huaning_file = os.path.join(current_dir, "excel", "华宁PayPal4.1-4.30.CSV")
    # if os.path.exists(huaning_file):
    #     csv_data_temp = get_csv_data(huaning_file)
    #     csv_data.extend(csv_data_temp)
    
    csv_data2 = get_csv_data(air_csv_file)
    
    # 处理PayPal数据
    pay_data2 = []
    for item in pay_data:
        is_repeat = False
        for csv_item in csv_data:
            is_same = csv_item.get("交易号") == item.get("支付订单号")
            if is_same:
                if csv_item.get("状态") == "已完成" and csv_item.get("类型") in checkout_types:
                    new_obj = {}
                    for key in header1:
                        new_obj[key] = csv_item.get(key, "")
                        # 将数值字段转换为浮点数
                        if key in ["总额", "费用", "净额"]:
                            try:
                                new_obj[key] = float(new_obj[key])
                            except (ValueError, TypeError):
                                new_obj[key] = 0.0
                    
                    new_obj["计数"] = 1
                    if is_repeat:
                        new_obj["计数"] = new_obj.get("计数", 0) + 1
                    
                    is_repeat = True
                    pay_data2.append(new_obj)
    
    # 处理云汇数据
    air_data2 = []
    for item in air_data:
        is_repeat = False
        for csv_item in csv_data2:
            is_same = csv_item.get("交易编号") == item.get("支付订单号")
            if is_same:
                if csv_item.get("交易状态") in checkout_types2:
                    new_obj = {}
                    for key in header2:
                        new_obj[key] = csv_item.get(key, "")
                        # 将数值字段转换为浮点数
                        if key in ["交易金额"]:
                            try:
                                new_obj[key] = float(new_obj[key])
                            except (ValueError, TypeError):
                                new_obj[key] = 0.0
                    
                    new_obj["计数"] = 1
                    if is_repeat:
                        new_obj["计数"] = new_obj.get("计数", 0) + 1
                    
                    is_repeat = True
                    air_data2.append(new_obj)
    
    # 创建多工作表的Excel文件
    multi_sheet_data = {
        'sheets': [
            {
                "name": "原始数据",
                "data": new_data,
                "options": {
                    "header": 1,
                    "autoFilter": True,
                }
            },
            {
                "name": "店铺筛Paypal",
                "data": pay_data,
                "options": {
                    "header": 1,
                    "autoFilter": True,
                }
            },
            {
                "name": "csv-PayPal",
                "data": pay_data2,
                "options": {
                    "header": 1,
                    "autoFilter": {
                        "column": "计数",
                    }
                }
            },
            {
                "name": "店铺筛云汇",
                "data": air_data,
                "options": {
                    "header": 1,
                    "autoFilter": True,
                }
            },
            {
                "name": "csv-云汇订单",
                "data": air_data2,
                "options": {
                    "header": 1,
                    "autoFilter": {
                        "column": "计数",
                    }
                }
            },
        ]
    }
    
    # 导出多工作表Excel文件
    output_file = create_filename(export_file_name)
    export_excel(multi_sheet_data, output_file)
    
    # 可选：计算货币汇总
    # currency_sum(pay_data, "货币单位", "订单总价")
    # currency_sum(pay_data2, "币种", "总额")
    # currency_sum(air_data2, "交易币种", "交易金额")


def parse_arguments():
    """
    解析命令行参数
    """
    parser = argparse.ArgumentParser(description='Excel数据处理工具')
    parser.add_argument('--excel', nargs='+', required=True, help='Excel文件路径，可以指定多个文件')
    parser.add_argument('--paypal', required=True, help='PayPal CSV文件路径')
    parser.add_argument('--air', required=True, help='云汇 CSV文件路径')
    parser.add_argument('--output', required=True, help='输出Excel文件名')
    return parser.parse_args()


if __name__ == "__main__":
    if len(sys.argv) > 1:
        # 命令行模式
        args = parse_arguments()
        main(args.excel, args.paypal, args.air, args.output)
    else:
        # 交互模式
        print("Excel数据处理工具")
        print("请输入以下信息：")
        
        # 获取Excel文件路径
        excel_files = []
        while True:
            excel_file = input("Excel文件路径 (输入空行结束): ")
            if not excel_file:
                break
            if os.path.exists(excel_file):
                excel_files.append(excel_file)
            else:
                print(f"文件不存在: {excel_file}")
        
        if not excel_files:
            print("错误：至少需要一个Excel文件")
            sys.exit(1)
        
        # 获取PayPal CSV文件路径
        while True:
            pay_csv_file = input("PayPal CSV文件路径: ")
            if os.path.exists(pay_csv_file):
                break
            print(f"文件不存在: {pay_csv_file}")
        
        # 获取云汇CSV文件路径
        while True:
            air_csv_file = input("云汇 CSV文件路径: ")
            if os.path.exists(air_csv_file):
                break
            print(f"文件不存在: {air_csv_file}")
        
        # 获取输出文件名
        export_file_name = input("输出Excel文件名 (默认: output.xlsx): ")
        if not export_file_name:
            export_file_name = "output.xlsx"
        
        # 执行主函数
        main(excel_files, pay_csv_file, air_csv_file, export_file_name)